package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import delegaciones.Delegacion;
import delegaciones.Provincia;
import empleados.Empleado;

public class JDBCDelegacionDAO implements IDelegacionDAO{
	
private Connection conn;
	
	public JDBCDelegacionDAO(){
		
		conn = ConnectionManager.getInstance().checkOut();
	
	}

	@Override
	public void delete(String provdelegacion, String cifempresa) {
		String sql = "DELETE FROM delegaciones WHERE (provdelegacion = ? AND cifempresa = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, provdelegacion);
            stmt.setString(2, cifempresa);         
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public List<Provincia> devuelveprovincias() {
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Provincia> listaprovincias = new LinkedList<Provincia>();
        
        String sql = "SELECT * FROM provincias ORDER BY provincia";
    
        try {
            stmt = conn.prepareStatement(sql);
            result = stmt.executeQuery();

            while (result.next()) {
                Provincia p = new Provincia();         
            	p.setNombre(result.getString("provincia")); 
                listaprovincias.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listaprovincias;
	}

	@Override
	public void insert(Delegacion d) {
		String sql = "INSERT INTO delegaciones (providelegacion, cifempresa, direccion, telefono) VALUES (?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, d.getProvincia());
            stmt.setString(2, d.getCIF());
            stmt.setString(3, d.getDireccion());
            stmt.setString(4, d.getTelefono());
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
		
	

	@Override
	public void insertempleadoendelegacion(Empleado e, Delegacion d) {
		String sql = "INSERT INTO empleadosdelegaciones (dniemp, cifempresa, provincia) VALUES (?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, e.getDNI());
            stmt.setString(2, d.getCIF());
            stmt.setString(3, d.getProvincia());
            stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
		
	}

	@Override
	public void modificadelegacion(Delegacion d) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE delegaciones SET direccion = ?, telefono = ?" +
				"WHERE (provdelegacion = ? AND cifempresa = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, d.getDireccion());
			stmt.setString(2, d.getTelefono());	
			stmt.setString(3, d.getProvincia());
			stmt.setString(4, d.getCIF());	
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
		
	}

	@Override
	public void modificadelegacionempleado(Empleado e, Delegacion d) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE empleadosdelegaciones SET cifempresa = ?, provincia = ?" +
				"WHERE (dniemp = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, d.getCIF());
			stmt.setString(2, d.getProvincia());
			stmt.setString(3, e.getDNI());			
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}

	@Override
	public Delegacion select(String provdelegacion, String cifempresa) {
		PreparedStatement stmt = null;
        ResultSet result = null; 
        Delegacion d = null;
       
        String sql = "SELECT * FROM delegaciones WHERE (provdelegacion = ? AND cifempresa = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, provdelegacion);
            stmt.setString(2, cifempresa);
            result = stmt.executeQuery();

            if(result.next()){
            	d = new Delegacion();
            	d.setProvincia(result.getString("provdelegacion"));
            	d.setCIF(result.getString("cifempresa"));
            	d.setDireccion(result.getString("direccion"));
            	d.setTelefono(result.getString("telefono"));
            	

            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return d;
	}

	@Override
	public Delegacion selectdelegacionempleado(String dni) {
		PreparedStatement stmt = null;
        ResultSet result = null; 
        Delegacion d = null;      
        String sql = "SELECT * FROM empleadosdelegaciones WHERE (dniemp = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            result = stmt.executeQuery();
            
            if(result.next()){
            	d = new Delegacion();
            	d.setCIF(result.getString("cifempresa"));
            	d.setProvincia(result.getString("provincia"));
            }
            
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return d;
	}

	@Override
	public List<Empleado> selectempleadosdelegacion(Delegacion d) {
		PreparedStatement stmt = null;
        ResultSet result = null;
        List<Empleado> listaempleados = new LinkedList<Empleado>();
        
        String sql = "SELECT * FROM empleadosdelegaciones WHERE (cifempresa = ? AND provincia = ?) ORDER BY dniemp";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, d.getCIF());
            stmt.setString(2, d.getProvincia());
            result = stmt.executeQuery();

            while (result.next()) {
                Empleado e = new Empleado();                
            	e.setDNI(result.getString("dniemp"));               
            	listaempleados.add(e);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return listaempleados;
	}
	
	public void finalize(){
		ConnectionManager.getInstance().checkIn(conn);
		
	}

}
